<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.2">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"wrr123.github.io","root":"/","scheme":"Muse","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.json"};
  </script>

  <meta name="description" content="常用函数 取余函数 mod 对时间格式或数字格式进行截取 trunc 对数字进行四舍五入 round(a1, a2)a1: 进行四舍五入的数字a2: 从小数点的左边还是右边几位数进行四舍五入（0表示四舍五入到整数位，正数1表示四舍五入到小数点右边1位，负数1表示四舍五入到小数点左边1位）  Tue Oct 29 11:23:05 CST 2019查询数据库的表空间？ select * from d">
<meta property="og:type" content="article">
<meta property="og:title" content="notebook-oracle">
<meta property="og:url" content="https://wrr123.github.io/2020/08/16/notebook-oracle/index.html">
<meta property="og:site_name" content="一缕烟气">
<meta property="og:description" content="常用函数 取余函数 mod 对时间格式或数字格式进行截取 trunc 对数字进行四舍五入 round(a1, a2)a1: 进行四舍五入的数字a2: 从小数点的左边还是右边几位数进行四舍五入（0表示四舍五入到整数位，正数1表示四舍五入到小数点右边1位，负数1表示四舍五入到小数点左边1位）  Tue Oct 29 11:23:05 CST 2019查询数据库的表空间？ select * from d">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2020-08-15T16:42:17.000Z">
<meta property="article:modified_time" content="2022-02-18T02:52:04.495Z">
<meta property="article:author" content="田园隐士">
<meta property="article:tag" content="notebook">
<meta name="twitter:card" content="summary">

<link rel="canonical" href="https://wrr123.github.io/2020/08/16/notebook-oracle/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>notebook-oracle | 一缕烟气</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

<link rel="alternate" href="/atom.xml" title="一缕烟气" type="application/atom+xml">
</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">一缕烟气</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">沧海月明珠有泪，蓝田日暖玉生烟</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>

  </li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup">
        <div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div id="search-result">
  <div id="no-result">
    <i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
  </div>
</div>

    </div>
  </div>

</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://wrr123.github.io/2020/08/16/notebook-oracle/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="田园隐士">
      <meta itemprop="description" content="talk is cheap, show me the code">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="一缕烟气">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          notebook-oracle
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2020-08-16 00:42:17" itemprop="dateCreated datePublished" datetime="2020-08-16T00:42:17+08:00">2020-08-16</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">更新于</span>
                <time title="修改时间：2022-02-18 10:52:04" itemprop="dateModified" datetime="2022-02-18T10:52:04+08:00">2022-02-18</time>
              </span>

          
            <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv" style="display: none;">
              <span class="post-meta-item-icon">
                <i class="fa fa-eye"></i>
              </span>
              <span class="post-meta-item-text">阅读次数：</span>
              <span id="busuanzi_value_page_pv"></span>
            </span><br>
            <span class="post-meta-item" title="本文字数">
              <span class="post-meta-item-icon">
                <i class="far fa-file-word"></i>
              </span>
                <span class="post-meta-item-text">本文字数：</span>
              <span>5k</span>
            </span>
            <span class="post-meta-item" title="阅读时长">
              <span class="post-meta-item-icon">
                <i class="far fa-clock"></i>
              </span>
                <span class="post-meta-item-text">阅读时长 &asymp;</span>
              <span>5 分钟</span>
            </span>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h2 id="常用函数"><a href="#常用函数" class="headerlink" title="常用函数"></a>常用函数</h2><ol>
<li>取余函数 mod</li>
<li>对时间格式或数字格式进行截取 trunc</li>
<li>对数字进行四舍五入 round(a1, a2)<br>a1: 进行四舍五入的数字<br>a2: 从小数点的左边还是右边几位数进行四舍五入（0表示四舍五入到整数位，正数1表示四舍五入到小数点右边1位，负数1表示四舍五入到小数点左边1位）</li>
</ol>
<h1 id="Tue-Oct-29-11-23-05-CST-2019"><a href="#Tue-Oct-29-11-23-05-CST-2019" class="headerlink" title="Tue Oct 29 11:23:05 CST 2019"></a>Tue Oct 29 11:23:05 CST 2019</h1><h2 id="查询数据库的表空间？"><a href="#查询数据库的表空间？" class="headerlink" title="查询数据库的表空间？"></a>查询数据库的表空间？</h2><ul>
<li><code>select * from dba_tablespaces t;</code></li>
</ul>
<h2 id="修改表字段信息"><a href="#修改表字段信息" class="headerlink" title="修改表字段信息"></a>修改表字段信息</h2><ul>
<li><code>alter table $tableName modify $columnName varchar2(20)</code></li>
</ul>
<h1 id="Thu-Oct-31-13-57-58-CST-2019"><a href="#Thu-Oct-31-13-57-58-CST-2019" class="headerlink" title="Thu Oct 31 13:57:58 CST 2019"></a>Thu Oct 31 13:57:58 CST 2019</h1><h2 id="oracle表的唯一性隐藏字段rowid"><a href="#oracle表的唯一性隐藏字段rowid" class="headerlink" title="oracle表的唯一性隐藏字段rowid"></a>oracle表的唯一性隐藏字段rowid</h2><h1 id="Fri-Nov-1-18-07-51-CST-2019"><a href="#Fri-Nov-1-18-07-51-CST-2019" class="headerlink" title="Fri Nov  1 18:07:51 CST 2019"></a>Fri Nov  1 18:07:51 CST 2019</h1><h2 id="ora-01847-月份中日的值必须介于1和当月最后一日之间？"><a href="#ora-01847-月份中日的值必须介于1和当月最后一日之间？" class="headerlink" title="ora-01847:月份中日的值必须介于1和当月最后一日之间？"></a>ora-01847:月份中日的值必须介于1和当月最后一日之间？</h2><h1 id="Sun-Nov-3-12-30-27-CST-2019"><a href="#Sun-Nov-3-12-30-27-CST-2019" class="headerlink" title="Sun Nov  3 12:30:27 CST 2019"></a>Sun Nov  3 12:30:27 CST 2019</h1><h2 id="以后给别人导表数据的时候，最好是表结构和表数据一起导，防止数据的类型出现各种带下不一致的问题。"><a href="#以后给别人导表数据的时候，最好是表结构和表数据一起导，防止数据的类型出现各种带下不一致的问题。" class="headerlink" title="以后给别人导表数据的时候，最好是表结构和表数据一起导，防止数据的类型出现各种带下不一致的问题。"></a>以后给别人导表数据的时候，最好是表结构和表数据一起导，防止数据的类型出现各种带下不一致的问题。</h2><h1 id="Mon-Nov-4-12-29-24-CST-2019"><a href="#Mon-Nov-4-12-29-24-CST-2019" class="headerlink" title="Mon Nov  4 12:29:24 CST 2019"></a>Mon Nov  4 12:29:24 CST 2019</h1><h2 id="oracle数据库的监听问题，描述查看监听的状态-lsnrctl-status-时，执行的很慢-查看1521端口时，显示监听已经启动了，但是查看监听的状态时，显示监听是没有启动的-有报错信息如下：-64-bit-Windows-Error-61-Unkonwn-error"><a href="#oracle数据库的监听问题，描述查看监听的状态-lsnrctl-status-时，执行的很慢-查看1521端口时，显示监听已经启动了，但是查看监听的状态时，显示监听是没有启动的-有报错信息如下：-64-bit-Windows-Error-61-Unkonwn-error" class="headerlink" title="oracle数据库的监听问题，描述查看监听的状态(lsnrctl status)时，执行的很慢;查看1521端口时，显示监听已经启动了，但是查看监听的状态时，显示监听是没有启动的;有报错信息如下：[64-bit Windows Error:61:Unkonwn error]"></a>oracle数据库的监听问题，描述<code>查看监听的状态(lsnrctl status)时，执行的很慢;查看1521端口时，显示监听已经启动了，但是查看监听的状态时，显示监听是没有启动的;有报错信息如下：[64-bit Windows Error:61:Unkonwn error]</code></h2><ul>
<li>检查监听的日志文件是不是太大了（如达到了4G），若是，需删除此日志文件</li>
<li>查看日志文件所在位置的sql<code>select name, value from v$diag_info;</code></li>
</ul>
<h1 id="Wed-Nov-6-11-04-41-CST-2019"><a href="#Wed-Nov-6-11-04-41-CST-2019" class="headerlink" title="Wed Nov  6 11:04:41 CST 2019"></a>Wed Nov  6 11:04:41 CST 2019</h1><h2 id="exp命令"><a href="#exp命令" class="headerlink" title="exp命令"></a>exp命令</h2><ul>
<li>导出指定数量的表数据文件<blockquote>
<p><code>exp cboms/cboms@32.202.32.21/cboms file=cboms.dmp log=cboms.log query=&quot;&#39;where rownum&lt;10001&#39;&quot; tables=&#39;%%&#39;</code></p>
</blockquote>
</li>
<li>导出数据库表结构<blockquote>
<p><code>exp cboms/cboms file=cboms_jiegou.dmp tables=%% rows=n</code></p>
</blockquote>
</li>
</ul>
<h2 id="新建数据库的表空间"><a href="#新建数据库的表空间" class="headerlink" title="新建数据库的表空间"></a>新建数据库的表空间</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--oracle创建用户并授权</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--创建用户临时表空间 </span></span><br><span class="line"><span class="keyword">create</span> temporary tablespace cboms_index tempfile <span class="string">&#x27;/home/oracle/oraData/cboms_index01.dbf&#x27;</span> size <span class="number">50</span>m autoextend <span class="keyword">on</span> next <span class="number">50</span>m maxsize <span class="number">20480</span>m extent management <span class="keyword">local</span>; </span><br><span class="line"></span><br><span class="line"><span class="comment">--创建用户表空间</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span>space cboms datafile <span class="string">&#x27;/home/oracle/oraData/cboms01.dbf&#x27;</span> size <span class="number">50</span>m autoextend <span class="keyword">on</span> next <span class="number">100</span>m maxsize unlimited(<span class="number">20480</span>m extent management <span class="keyword">local</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">--创建用户</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> cboms identified <span class="keyword">by</span> cboms <span class="keyword">default</span> tablespace cboms temporary tablespace cboms_index;</span><br><span class="line"><span class="comment">--授予权限</span></span><br><span class="line"><span class="keyword">grant</span> unlimited tablespace <span class="keyword">to</span> cboms;<span class="comment">--授予cboms用户使用表空间的权限</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">create</span> session <span class="keyword">to</span> cboms;<span class="comment">--授予cboms用户创建session的权限，即登陆权限</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">ALL</span> privileges <span class="keyword">to</span> cboms;<span class="comment">--这条比较重要，授予所有权限(all)给用户(cboms)</span></span><br><span class="line"><span class="keyword">GRANT</span> DBA <span class="keyword">TO</span> cboms;</span><br><span class="line"></span><br><span class="line"><span class="comment">--查看权限</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> user_sys_privs;<span class="comment">--查看当前用户所有权限</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> DBA_SYS_PRIVS <span class="keyword">WHERE</span> GRANTEE <span class="keyword">IN</span> (<span class="string">&#x27;cboms&#x27;</span>);</span><br></pre></td></tr></table></figure>
<h2 id="查看表空间"><a href="#查看表空间" class="headerlink" title="查看表空间"></a>查看表空间</h2><p><code>SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;</code></p>
<h2 id="新增表空间"><a href="#新增表空间" class="headerlink" title="新增表空间"></a>新增表空间</h2><p><code>alter tablespace cboms add datafile  &#39;/home/oracle/oraData/cboms02.dbf&#39; size 50m autoextend on next 100m maxsize 20480m;</code></p>
<h2 id="删除用户和表空间"><a href="#删除用户和表空间" class="headerlink" title="删除用户和表空间"></a>删除用户和表空间</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">删除用户和表空间,对于单个<span class="keyword">user</span>和tablespace 来说， 可以使用如下命令来完成。</span><br><span class="line">步骤一： 删除<span class="keyword">user</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span> ×× cascade</span><br><span class="line">如：<span class="keyword">drop</span> <span class="keyword">user</span> SMCHANNEL CASCADE</span><br><span class="line">说明： 删除了<span class="keyword">user</span>，只是删除了该<span class="keyword">user</span>下的schema objects，是不会删除相应的tablespace的。</span><br><span class="line">步骤二： 删除tablespace</span><br><span class="line"><span class="keyword">DROP</span> TABLESPACE tablespace_name INCLUDING CONTENTS <span class="keyword">AND</span> DATAFILES;</span><br></pre></td></tr></table></figure>
<h2 id="imp"><a href="#imp" class="headerlink" title="imp"></a>imp</h2><h3 id="IMP-00015-following-statement-failed-because-the-object-already-exists"><a href="#IMP-00015-following-statement-failed-because-the-object-already-exists" class="headerlink" title="IMP-00015: following statement failed because the object already exists"></a>IMP-00015: following statement failed because the object already exists</h3><ul>
<li>设置参数<code>ignore=y</code>(忽略创建错误)<h3 id="导入指定表的数据"><a href="#导入指定表的数据" class="headerlink" title="导入指定表的数据"></a>导入指定表的数据</h3><code>imp userid=cboms/cboms file=wxrcb_data/cboms_data.dmp tables=&#39;sys_data_dict,sys_data_dict_type&#39; log=imp.log data_only=y</code></li>
</ul>
<h2 id="可以判断一个字段以数字结尾吗？"><a href="#可以判断一个字段以数字结尾吗？" class="headerlink" title="可以判断一个字段以数字结尾吗？"></a>可以判断一个字段以数字结尾吗？</h2><ul>
<li>正则表达式的like <code>regexp_like</code></li>
</ul>
<h2 id="函数：将不同行的字段合并到同一列上的？"><a href="#函数：将不同行的字段合并到同一列上的？" class="headerlink" title="函数：将不同行的字段合并到同一列上的？"></a>函数：将不同行的字段合并到同一列上的？</h2><ul>
<li>wm_concat(column) <code>默认以逗号分隔</code></li>
<li>listagg(cloumnA, ‘,’) within group(rder by columnB)</li>
</ul>
<h1 id="Thu-Nov-7-10-03-32-CST-2019"><a href="#Thu-Nov-7-10-03-32-CST-2019" class="headerlink" title="Thu Nov  7 10:03:32 CST 2019"></a>Thu Nov  7 10:03:32 CST 2019</h1><h2 id="新增序列"><a href="#新增序列" class="headerlink" title="新增序列"></a>新增序列</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> sequence seq_arc_efile_zskwj</span><br><span class="line">minvalue <span class="number">1</span></span><br><span class="line">maxvalue <span class="number">999999999999999999</span></span><br><span class="line"><span class="keyword">start</span> <span class="keyword">with</span> <span class="number">621</span></span><br><span class="line">increment <span class="keyword">by</span> <span class="number">1</span></span><br><span class="line">nocache;</span><br></pre></td></tr></table></figure>
<h1 id="Fri-Nov-8-09-33-32-CST-2019"><a href="#Fri-Nov-8-09-33-32-CST-2019" class="headerlink" title="Fri Nov  8 09:33:32 CST 2019"></a>Fri Nov  8 09:33:32 CST 2019</h1><h2 id="在不同的表中插入语句或者在同一张表中插入多条数据？"><a href="#在不同的表中插入语句或者在同一张表中插入多条数据？" class="headerlink" title="在不同的表中插入语句或者在同一张表中插入多条数据？"></a>在不同的表中插入语句或者在同一张表中插入多条数据？</h2><h1 id="Sun-Nov-10-11-10-58-CST-2019"><a href="#Sun-Nov-10-11-10-58-CST-2019" class="headerlink" title="Sun Nov 10 11:10:58 CST 2019"></a>Sun Nov 10 11:10:58 CST 2019</h1><h2 id="给表或者字段添加注释"><a href="#给表或者字段添加注释" class="headerlink" title="给表或者字段添加注释"></a>给表或者字段添加注释</h2><ol>
<li><code>comment on table tableName is &#39;&#39;</code></li>
<li><code>comment on column tableName.columnName is &#39;&#39;</code></li>
</ol>
<h1 id="Mon-Nov-11-10-36-34-CST-2019"><a href="#Mon-Nov-11-10-36-34-CST-2019" class="headerlink" title="Mon Nov 11 10:36:34 CST 2019"></a>Mon Nov 11 10:36:34 CST 2019</h1><h2 id="del格式文件导入到oracle数据库中"><a href="#del格式文件导入到oracle数据库中" class="headerlink" title="del格式文件导入到oracle数据库中"></a>del格式文件导入到oracle数据库中</h2><h1 id="Wed-Nov-20-12-53-12-CST-2019"><a href="#Wed-Nov-20-12-53-12-CST-2019" class="headerlink" title="Wed Nov 20 12:53:12 CST 2019"></a>Wed Nov 20 12:53:12 CST 2019</h1><h2 id="换行符表示"><a href="#换行符表示" class="headerlink" title="换行符表示"></a>换行符表示</h2><blockquote>
<p>chr(13) 回车符<br>chr(10) 换行符<br>chr(9) 制表符</p>
<ul>
<li><h1 id="Thu-Dec-5-09-53-50-CST-2019"><a href="#Thu-Dec-5-09-53-50-CST-2019" class="headerlink" title="Thu Dec  5 09:53:50 CST 2019"></a>Thu Dec  5 09:53:50 CST 2019</h1></li>
</ul>
<ol>
<li>判断字符串中是否含有某个字符的函数？<br><code>instr(str, search_str, [, start_position][, nth_appearance]) instr(源字符串， 目标字符串[, 开始位置][, 匹配序号])</code></li>
<li>IF判断的多重分支？<br>一、单个IF<br>1、<br>if a=…  then<br>………<br>end if;<br>2、<br>if a=… then<br>……<br>else<br>….<br>end if;<br>二、多个IF<br>if a=..  then<br>……<br>elsif a=..  then<br>….<br>end if;</li>
</ol>
</blockquote>
<h1 id="Thu-Dec-12-10-09-08-CST-2019"><a href="#Thu-Dec-12-10-09-08-CST-2019" class="headerlink" title="Thu Dec 12 10:09:08 CST 2019"></a>Thu Dec 12 10:09:08 CST 2019</h1><h2 id="IMP-00002-failed-to-open-ers1-dmp-for-read"><a href="#IMP-00002-failed-to-open-ers1-dmp-for-read" class="headerlink" title="IMP-00002: failed to open ers1.dmp for read ??"></a>IMP-00002: failed to open ers1.dmp for read ??</h2><h2 id="ORA-12705-Cannot-access-NLS-data-files-or-invalid-environment-specified"><a href="#ORA-12705-Cannot-access-NLS-data-files-or-invalid-environment-specified" class="headerlink" title="ORA-12705: Cannot access NLS data files or invalid environment specified ??"></a>ORA-12705: Cannot access NLS data files or invalid environment specified ??</h2><h2 id="oracle数据库，sqlplus连接乱码问题？"><a href="#oracle数据库，sqlplus连接乱码问题？" class="headerlink" title="oracle数据库，sqlplus连接乱码问题？"></a>oracle数据库，sqlplus连接乱码问题？</h2><ul>
<li>linux：设置环境变量<code>NLS_LANG</code>,等于<code>AMERICAN_AMERICA.AL32UTF8</code></li>
<li>windows: 设置环境变量<code>NLS_LANG</code>,等于<code>SIMPLIFIED CHINESE_CHINA.ZHS16GBK</code></li>
</ul>
<h2 id="start-with-connect-by-prior-用法？"><a href="#start-with-connect-by-prior-用法？" class="headerlink" title="start with... connect by prior...用法？"></a><code>start with... connect by prior...</code>用法？</h2><p>  以哪条数据开始，以父数据(prior)的字段作为子数据的另一个字段,依次递归查询，为一个树形结构。</p>
<h1 id="Sat-Dec-14-19-55-06-CST-2019"><a href="#Sat-Dec-14-19-55-06-CST-2019" class="headerlink" title="Sat Dec 14 19:55:06 CST 2019"></a>Sat Dec 14 19:55:06 CST 2019</h1><h2 id="ORA-01033-ORACLE-initialization-or-shutdown"><a href="#ORA-01033-ORACLE-initialization-or-shutdown" class="headerlink" title="ORA-01033:ORACLE initialization or shutdown ??"></a>ORA-01033:ORACLE initialization or shutdown ??</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">shutdown normal;</span><br><span class="line">startup mount;</span><br><span class="line">alter database open;</span><br><span class="line"></span><br><span class="line"># Mon Dec 16 09:03:54 CST 2019</span><br><span class="line">## ORA-03113: end-of-file on communication channel??</span><br><span class="line">[](https://blog.51cto.com/mister/1740508)</span><br><span class="line">​```sqlplus</span><br><span class="line">startup mount;</span><br><span class="line">startup nomount;</span><br><span class="line">exit;</span><br><span class="line">sqlplus / as sysdba</span><br><span class="line">startup mount;</span><br><span class="line">alter database open;</span><br></pre></td></tr></table></figure>
<h1 id="Mon-Dec-16-18-40-39-CST-2019"><a href="#Mon-Dec-16-18-40-39-CST-2019" class="headerlink" title="Mon Dec 16 18:40:39 CST 2019"></a>Mon Dec 16 18:40:39 CST 2019</h1><h2 id="命令行查看函数或者存储过程的源代码？"><a href="#命令行查看函数或者存储过程的源代码？" class="headerlink" title="命令行查看函数或者存储过程的源代码？"></a>命令行查看函数或者存储过程的源代码？</h2><ul>
<li><code>select text from (all_source)user_source where name=upper(&#39;swap_drb&#39;) order by line;</code></li>
<li><a target="_blank" rel="noopener" href="https://www.cnblogs.com/saptechnique/archive/2013/03/19/2969906.html">oralce-sqlplus命令详解</a></li>
</ul>
<h2 id="ORA-06575-程序包或函数-FUN-GET-SFZHM-BY-CZY-处于无效状态"><a href="#ORA-06575-程序包或函数-FUN-GET-SFZHM-BY-CZY-处于无效状态" class="headerlink" title="ORA-06575: 程序包或函数 FUN_GET_SFZHM_BY_CZY 处于无效状态?"></a>ORA-06575: 程序包或函数 FUN_GET_SFZHM_BY_CZY 处于无效状态?</h2><ul>
<li>错误原因<br>程序包或者函数编译不通过。</li>
</ul>
<h2 id="函数创建的基本格式？"><a href="#函数创建的基本格式？" class="headerlink" title="函数创建的基本格式？"></a>函数创建的基本格式？</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">创建函数的基本格式 create or replace function 函数名称(参数1 ，参数2) return 参数类型 is </span><br><span class="line">begin</span><br><span class="line">end</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">--创建函数语法</span><br><span class="line">create [or replace] function [schema.]function_name</span><br><span class="line">(函数参数列表) --参数有IN、OUT、IN OUT三种类型；IN代表需要输入的参数，OUT代表要返回的参数，IN OUT代表即是输入参数也是返回参数。</span><br><span class="line">return datetype--返回类型</span><br><span class="line">[is | as ]---任选一个，后面跟pLSQL代码块</span><br><span class="line">[declare]--有参数时使用关键字</span><br><span class="line">begin</span><br><span class="line">  --执行的SQL</span><br><span class="line">end;</span><br></pre></td></tr></table></figure>
<h1 id="2019年12月31日-星期二-14时05分21秒-CST"><a href="#2019年12月31日-星期二-14时05分21秒-CST" class="headerlink" title="2019年12月31日 星期二 14时05分21秒 CST"></a>2019年12月31日 星期二 14时05分21秒 CST</h1><h2 id="for-in-loop-statement"><a href="#for-in-loop-statement" class="headerlink" title="for in loop statement"></a>for in loop statement</h2><p>oracle常见的三种循环结构</p>
<ul>
<li>loop statement end loop</li>
<li>while boolean expression loop statement end loop</li>
<li>for indexname in () loop statement end loop<br>examples<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">declare</span><br><span class="line">    v_num int;</span><br><span class="line">begin</span><br><span class="line">    for v_num in (reverse) 1..10 loop</span><br><span class="line">        dbms_output.PUT_LINE(&#x27;num:&#x27;||v_num);</span><br><span class="line">        end loop;</span><br><span class="line">end;</span><br></pre></td></tr></table></figure>
<h2 id="oracle打印日志输出"><a href="#oracle打印日志输出" class="headerlink" title="oracle打印日志输出"></a>oracle打印日志输出</h2><code>dbms_output.put_line()</code></li>
</ul>
<h2 id="oracle中join连接的几种方式"><a href="#oracle中join连接的几种方式" class="headerlink" title="oracle中join连接的几种方式"></a>oracle中join连接的几种方式</h2><ul>
<li>内连接(inner join),其中inner可以省略</li>
<li>外连接(left outer join,right outer join)，其中outer可以省略</li>
<li>…</li>
</ul>
<h2 id="table-函数的用法"><a href="#table-函数的用法" class="headerlink" title="table()函数的用法"></a>table()函数的用法</h2><ul>
<li>结合数组</li>
<li>结合pipelined函数</li>
<li>结合系统包<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">--1.</span><br><span class="line">explain plan for select * from usr_users t;</span><br><span class="line">select * from table(dbms_xplan.display);</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="plan-table-is-old-version的处理方法"><a href="#plan-table-is-old-version的处理方法" class="headerlink" title="plan_table is old version的处理方法"></a>plan_table is old version的处理方法</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">drop table plan_table;</span><br><span class="line">@?/rdbms/admin/utlxplan.sql;</span><br></pre></td></tr></table></figure>
<h2 id="使用执行计划进行sql优化"><a href="#使用执行计划进行sql优化" class="headerlink" title="使用执行计划进行sql优化"></a>使用执行计划进行sql优化</h2><ul>
<li>查看总COST，获得资源耗费的总体印象 </li>
<li>按照从左至右，从上至下的方法，了解执行计划的执行步骤 </li>
<li>分析表的访问方式<br>全表扫描（TABLE ACCESS FULL）和索引扫描(INDEX SCAN)</li>
<li>分析表的连接方式和连接顺序<br>嵌套循环（NESTED LOOPS）、哈希连接（HASH JOIN）和排序-合并连接（SORT MERGE JOIN）</li>
</ul>
<h1 id="2020年-1月-2日-星期四-09时01分39秒-CST"><a href="#2020年-1月-2日-星期四-09时01分39秒-CST" class="headerlink" title="2020年 1月 2日 星期四 09时01分39秒 CST"></a>2020年 1月 2日 星期四 09时01分39秒 CST</h1><h2 id><a href="#" class="headerlink" title=" "></a> </h2>
    </div>

    
    
    
        

  <div class="followme">
    <p>欢迎关注我的其它发布渠道</p>

    <div class="social-list">

        <div class="social-item">
          <a target="_blank" class="social-link" href="/atom.xml">
            <span class="icon">
              <i class="fa fa-rss"></i>
            </span>

            <span class="label">RSS</span>
          </a>
        </div>
    </div>
  </div>


      <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/notebook/" rel="tag"># notebook</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2020/08/16/notebook-plantUML/" rel="prev" title="notebook-plantUML">
      <i class="fa fa-chevron-left"></i> notebook-plantUML
    </a></div>
      <div class="post-nav-item">
    <a href="/2020/08/16/notebook-maven/" rel="next" title="notebook-maven">
      notebook-maven <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0"><span class="nav-number">1.</span> <span class="nav-text">常用函数</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Tue-Oct-29-11-23-05-CST-2019"><span class="nav-number"></span> <span class="nav-text">Tue Oct 29 11:23:05 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E8%A1%A8%E7%A9%BA%E9%97%B4%EF%BC%9F"><span class="nav-number">1.</span> <span class="nav-text">查询数据库的表空间？</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BF%AE%E6%94%B9%E8%A1%A8%E5%AD%97%E6%AE%B5%E4%BF%A1%E6%81%AF"><span class="nav-number">2.</span> <span class="nav-text">修改表字段信息</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Thu-Oct-31-13-57-58-CST-2019"><span class="nav-number"></span> <span class="nav-text">Thu Oct 31 13:57:58 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#oracle%E8%A1%A8%E7%9A%84%E5%94%AF%E4%B8%80%E6%80%A7%E9%9A%90%E8%97%8F%E5%AD%97%E6%AE%B5rowid"><span class="nav-number">1.</span> <span class="nav-text">oracle表的唯一性隐藏字段rowid</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Fri-Nov-1-18-07-51-CST-2019"><span class="nav-number"></span> <span class="nav-text">Fri Nov  1 18:07:51 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#ora-01847-%E6%9C%88%E4%BB%BD%E4%B8%AD%E6%97%A5%E7%9A%84%E5%80%BC%E5%BF%85%E9%A1%BB%E4%BB%8B%E4%BA%8E1%E5%92%8C%E5%BD%93%E6%9C%88%E6%9C%80%E5%90%8E%E4%B8%80%E6%97%A5%E4%B9%8B%E9%97%B4%EF%BC%9F"><span class="nav-number">1.</span> <span class="nav-text">ora-01847:月份中日的值必须介于1和当月最后一日之间？</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Sun-Nov-3-12-30-27-CST-2019"><span class="nav-number"></span> <span class="nav-text">Sun Nov  3 12:30:27 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BB%A5%E5%90%8E%E7%BB%99%E5%88%AB%E4%BA%BA%E5%AF%BC%E8%A1%A8%E6%95%B0%E6%8D%AE%E7%9A%84%E6%97%B6%E5%80%99%EF%BC%8C%E6%9C%80%E5%A5%BD%E6%98%AF%E8%A1%A8%E7%BB%93%E6%9E%84%E5%92%8C%E8%A1%A8%E6%95%B0%E6%8D%AE%E4%B8%80%E8%B5%B7%E5%AF%BC%EF%BC%8C%E9%98%B2%E6%AD%A2%E6%95%B0%E6%8D%AE%E7%9A%84%E7%B1%BB%E5%9E%8B%E5%87%BA%E7%8E%B0%E5%90%84%E7%A7%8D%E5%B8%A6%E4%B8%8B%E4%B8%8D%E4%B8%80%E8%87%B4%E7%9A%84%E9%97%AE%E9%A2%98%E3%80%82"><span class="nav-number">1.</span> <span class="nav-text">以后给别人导表数据的时候，最好是表结构和表数据一起导，防止数据的类型出现各种带下不一致的问题。</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Mon-Nov-4-12-29-24-CST-2019"><span class="nav-number"></span> <span class="nav-text">Mon Nov  4 12:29:24 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#oracle%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E7%9B%91%E5%90%AC%E9%97%AE%E9%A2%98%EF%BC%8C%E6%8F%8F%E8%BF%B0%E6%9F%A5%E7%9C%8B%E7%9B%91%E5%90%AC%E7%9A%84%E7%8A%B6%E6%80%81-lsnrctl-status-%E6%97%B6%EF%BC%8C%E6%89%A7%E8%A1%8C%E7%9A%84%E5%BE%88%E6%85%A2-%E6%9F%A5%E7%9C%8B1521%E7%AB%AF%E5%8F%A3%E6%97%B6%EF%BC%8C%E6%98%BE%E7%A4%BA%E7%9B%91%E5%90%AC%E5%B7%B2%E7%BB%8F%E5%90%AF%E5%8A%A8%E4%BA%86%EF%BC%8C%E4%BD%86%E6%98%AF%E6%9F%A5%E7%9C%8B%E7%9B%91%E5%90%AC%E7%9A%84%E7%8A%B6%E6%80%81%E6%97%B6%EF%BC%8C%E6%98%BE%E7%A4%BA%E7%9B%91%E5%90%AC%E6%98%AF%E6%B2%A1%E6%9C%89%E5%90%AF%E5%8A%A8%E7%9A%84-%E6%9C%89%E6%8A%A5%E9%94%99%E4%BF%A1%E6%81%AF%E5%A6%82%E4%B8%8B%EF%BC%9A-64-bit-Windows-Error-61-Unkonwn-error"><span class="nav-number">1.</span> <span class="nav-text">oracle数据库的监听问题，描述查看监听的状态(lsnrctl status)时，执行的很慢;查看1521端口时，显示监听已经启动了，但是查看监听的状态时，显示监听是没有启动的;有报错信息如下：[64-bit Windows Error:61:Unkonwn error]</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Wed-Nov-6-11-04-41-CST-2019"><span class="nav-number"></span> <span class="nav-text">Wed Nov  6 11:04:41 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#exp%E5%91%BD%E4%BB%A4"><span class="nav-number">1.</span> <span class="nav-text">exp命令</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%96%B0%E5%BB%BA%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E8%A1%A8%E7%A9%BA%E9%97%B4"><span class="nav-number">2.</span> <span class="nav-text">新建数据库的表空间</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9F%A5%E7%9C%8B%E8%A1%A8%E7%A9%BA%E9%97%B4"><span class="nav-number">3.</span> <span class="nav-text">查看表空间</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%96%B0%E5%A2%9E%E8%A1%A8%E7%A9%BA%E9%97%B4"><span class="nav-number">4.</span> <span class="nav-text">新增表空间</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%A0%E9%99%A4%E7%94%A8%E6%88%B7%E5%92%8C%E8%A1%A8%E7%A9%BA%E9%97%B4"><span class="nav-number">5.</span> <span class="nav-text">删除用户和表空间</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#imp"><span class="nav-number">6.</span> <span class="nav-text">imp</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#IMP-00015-following-statement-failed-because-the-object-already-exists"><span class="nav-number">6.1.</span> <span class="nav-text">IMP-00015: following statement failed because the object already exists</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%AF%BC%E5%85%A5%E6%8C%87%E5%AE%9A%E8%A1%A8%E7%9A%84%E6%95%B0%E6%8D%AE"><span class="nav-number">6.2.</span> <span class="nav-text">导入指定表的数据</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%8F%AF%E4%BB%A5%E5%88%A4%E6%96%AD%E4%B8%80%E4%B8%AA%E5%AD%97%E6%AE%B5%E4%BB%A5%E6%95%B0%E5%AD%97%E7%BB%93%E5%B0%BE%E5%90%97%EF%BC%9F"><span class="nav-number">7.</span> <span class="nav-text">可以判断一个字段以数字结尾吗？</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%87%BD%E6%95%B0%EF%BC%9A%E5%B0%86%E4%B8%8D%E5%90%8C%E8%A1%8C%E7%9A%84%E5%AD%97%E6%AE%B5%E5%90%88%E5%B9%B6%E5%88%B0%E5%90%8C%E4%B8%80%E5%88%97%E4%B8%8A%E7%9A%84%EF%BC%9F"><span class="nav-number">8.</span> <span class="nav-text">函数：将不同行的字段合并到同一列上的？</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Thu-Nov-7-10-03-32-CST-2019"><span class="nav-number"></span> <span class="nav-text">Thu Nov  7 10:03:32 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%96%B0%E5%A2%9E%E5%BA%8F%E5%88%97"><span class="nav-number">1.</span> <span class="nav-text">新增序列</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Fri-Nov-8-09-33-32-CST-2019"><span class="nav-number"></span> <span class="nav-text">Fri Nov  8 09:33:32 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%9C%A8%E4%B8%8D%E5%90%8C%E7%9A%84%E8%A1%A8%E4%B8%AD%E6%8F%92%E5%85%A5%E8%AF%AD%E5%8F%A5%E6%88%96%E8%80%85%E5%9C%A8%E5%90%8C%E4%B8%80%E5%BC%A0%E8%A1%A8%E4%B8%AD%E6%8F%92%E5%85%A5%E5%A4%9A%E6%9D%A1%E6%95%B0%E6%8D%AE%EF%BC%9F"><span class="nav-number">1.</span> <span class="nav-text">在不同的表中插入语句或者在同一张表中插入多条数据？</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Sun-Nov-10-11-10-58-CST-2019"><span class="nav-number"></span> <span class="nav-text">Sun Nov 10 11:10:58 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%BB%99%E8%A1%A8%E6%88%96%E8%80%85%E5%AD%97%E6%AE%B5%E6%B7%BB%E5%8A%A0%E6%B3%A8%E9%87%8A"><span class="nav-number">1.</span> <span class="nav-text">给表或者字段添加注释</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Mon-Nov-11-10-36-34-CST-2019"><span class="nav-number"></span> <span class="nav-text">Mon Nov 11 10:36:34 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#del%E6%A0%BC%E5%BC%8F%E6%96%87%E4%BB%B6%E5%AF%BC%E5%85%A5%E5%88%B0oracle%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD"><span class="nav-number">1.</span> <span class="nav-text">del格式文件导入到oracle数据库中</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Wed-Nov-20-12-53-12-CST-2019"><span class="nav-number"></span> <span class="nav-text">Wed Nov 20 12:53:12 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%8D%A2%E8%A1%8C%E7%AC%A6%E8%A1%A8%E7%A4%BA"><span class="nav-number">1.</span> <span class="nav-text">换行符表示</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Thu-Dec-5-09-53-50-CST-2019"><span class="nav-number"></span> <span class="nav-text">Thu Dec  5 09:53:50 CST 2019</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Thu-Dec-12-10-09-08-CST-2019"><span class="nav-number"></span> <span class="nav-text">Thu Dec 12 10:09:08 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#IMP-00002-failed-to-open-ers1-dmp-for-read"><span class="nav-number">1.</span> <span class="nav-text">IMP-00002: failed to open ers1.dmp for read ??</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#ORA-12705-Cannot-access-NLS-data-files-or-invalid-environment-specified"><span class="nav-number">2.</span> <span class="nav-text">ORA-12705: Cannot access NLS data files or invalid environment specified ??</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#oracle%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%8Csqlplus%E8%BF%9E%E6%8E%A5%E4%B9%B1%E7%A0%81%E9%97%AE%E9%A2%98%EF%BC%9F"><span class="nav-number">3.</span> <span class="nav-text">oracle数据库，sqlplus连接乱码问题？</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#start-with-connect-by-prior-%E7%94%A8%E6%B3%95%EF%BC%9F"><span class="nav-number">4.</span> <span class="nav-text">start with... connect by prior...用法？</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Sat-Dec-14-19-55-06-CST-2019"><span class="nav-number"></span> <span class="nav-text">Sat Dec 14 19:55:06 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#ORA-01033-ORACLE-initialization-or-shutdown"><span class="nav-number">1.</span> <span class="nav-text">ORA-01033:ORACLE initialization or shutdown ??</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Mon-Dec-16-18-40-39-CST-2019"><span class="nav-number"></span> <span class="nav-text">Mon Dec 16 18:40:39 CST 2019</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%91%BD%E4%BB%A4%E8%A1%8C%E6%9F%A5%E7%9C%8B%E5%87%BD%E6%95%B0%E6%88%96%E8%80%85%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%9A%84%E6%BA%90%E4%BB%A3%E7%A0%81%EF%BC%9F"><span class="nav-number">1.</span> <span class="nav-text">命令行查看函数或者存储过程的源代码？</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#ORA-06575-%E7%A8%8B%E5%BA%8F%E5%8C%85%E6%88%96%E5%87%BD%E6%95%B0-FUN-GET-SFZHM-BY-CZY-%E5%A4%84%E4%BA%8E%E6%97%A0%E6%95%88%E7%8A%B6%E6%80%81"><span class="nav-number">2.</span> <span class="nav-text">ORA-06575: 程序包或函数 FUN_GET_SFZHM_BY_CZY 处于无效状态?</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%87%BD%E6%95%B0%E5%88%9B%E5%BB%BA%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%A0%BC%E5%BC%8F%EF%BC%9F"><span class="nav-number">3.</span> <span class="nav-text">函数创建的基本格式？</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#2019%E5%B9%B412%E6%9C%8831%E6%97%A5-%E6%98%9F%E6%9C%9F%E4%BA%8C-14%E6%97%B605%E5%88%8621%E7%A7%92-CST"><span class="nav-number"></span> <span class="nav-text">2019年12月31日 星期二 14时05分21秒 CST</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#for-in-loop-statement"><span class="nav-number">1.</span> <span class="nav-text">for in loop statement</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#oracle%E6%89%93%E5%8D%B0%E6%97%A5%E5%BF%97%E8%BE%93%E5%87%BA"><span class="nav-number">2.</span> <span class="nav-text">oracle打印日志输出</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#oracle%E4%B8%ADjoin%E8%BF%9E%E6%8E%A5%E7%9A%84%E5%87%A0%E7%A7%8D%E6%96%B9%E5%BC%8F"><span class="nav-number">3.</span> <span class="nav-text">oracle中join连接的几种方式</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#table-%E5%87%BD%E6%95%B0%E7%9A%84%E7%94%A8%E6%B3%95"><span class="nav-number">4.</span> <span class="nav-text">table()函数的用法</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#plan-table-is-old-version%E7%9A%84%E5%A4%84%E7%90%86%E6%96%B9%E6%B3%95"><span class="nav-number">5.</span> <span class="nav-text">plan_table is old version的处理方法</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92%E8%BF%9B%E8%A1%8Csql%E4%BC%98%E5%8C%96"><span class="nav-number">6.</span> <span class="nav-text">使用执行计划进行sql优化</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#2020%E5%B9%B4-1%E6%9C%88-2%E6%97%A5-%E6%98%9F%E6%9C%9F%E5%9B%9B-09%E6%97%B601%E5%88%8639%E7%A7%92-CST"><span class="nav-number"></span> <span class="nav-text">2020年 1月 2日 星期四 09时01分39秒 CST</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link"><span class="nav-number">1.</span> <span class="nav-text"> </span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">田园隐士</p>
  <div class="site-description" itemprop="description">talk is cheap, show me the code</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">347</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
        <span class="site-state-item-count">53</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
        <span class="site-state-item-count">115</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2022</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">田园隐士</span>
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-chart-area"></i>
    </span>
    <span title="站点总字数">587k</span>
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-coffee"></i>
    </span>
    <span title="站点阅读时长">8:53</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://muse.theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Muse</a> 强力驱动
  </div>

        
<div class="busuanzi-count">
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span class="post-meta-item" id="busuanzi_container_site_uv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-user"></i>
      </span>
      <span class="site-uv" title="总访客量">
        <span id="busuanzi_value_site_uv"></span>
      </span>
    </span>
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item" id="busuanzi_container_site_pv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-eye"></i>
      </span>
      <span class="site-pv" title="总访问量">
        <span id="busuanzi_value_site_pv"></span>
      </span>
    </span>
</div>








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/muse.js"></script>


<script src="/js/next-boot.js"></script>




  




  
<script src="/js/local-search.js"></script>













  

  

  

</body>
</html>
